﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Logging;
using Dapper;

namespace Kingzad.MicroService.BusinessCore.Providers
{

    public interface ISqlServerProvider
    {
        Task<T> GetCellValueAsync<T>(string sql, object para, CommandType type);
        Task<IEnumerable<T>> GetDataAsync<T>(string sql, object para, CommandType type);
        Task<int> ExecuteAsync(string sql, object para, CommandType type);
    }
    public class SqlServerProvider : ISqlServerProvider
    {
        private readonly ILogger<SqlServerProvider> _logger;
        private readonly ISqlConnectionFactory _sqlConnectionFactory;

        public SqlServerProvider(ILogger<SqlServerProvider> logger, ISqlConnectionFactory sqlConnectionFactory)
        {
            _logger = logger;
            _sqlConnectionFactory = sqlConnectionFactory;
        }

        public async Task<T> GetCellValueAsync<T>(string sql, object para, CommandType type)
        {
            using (var con = _sqlConnectionFactory.GetConnection())
            {
                try
                {
                    return await con.ExecuteScalarAsync<T>(sql, para, null, null, type).ConfigureAwait(false);
                }
                catch (Exception e)
                {
                    _logger.LogError(e, "DB Execution Error!" + e.Message);
                    return default;
                }
            }
        }

        public async Task<IEnumerable<T>> GetDataAsync<T>(string sql, object para, CommandType type)
        {
            using (var con = _sqlConnectionFactory.GetConnection())
            {
                try
                {
                    return await con.QueryAsync<T>(sql, para, commandType: type).ConfigureAwait(false) ?? Enumerable.Empty<T>();
                }
                catch (Exception e)
                {
                    _logger.LogError(e, "DB Execution Error!" + e.Message);
                    return Enumerable.Empty<T>();
                }
            }
        }

        public async Task<int> ExecuteAsync(string sql, object para, CommandType type)
        {
            using (var con = _sqlConnectionFactory.GetConnection())
            {
                try
                {
                    return await con.ExecuteAsync(sql, para, commandType: type).ConfigureAwait(false);
                }
                catch (Exception e)
                {
                    _logger.LogError(e, "DB Execution Error!" + e.Message);
                    return 0;
                }
            }
        }
    }
}
